/*
Copyright (c) 2012, Qusai Al Abdallah and Omar Fawzi 
 * All rights reserved.

Redistribution and use in source and binary forms, with or without modification, 
are permitted provided that the following conditions are met:

- Redistributions of source code must retain the above copyright notice, this 
list of conditions and the following disclaimer.

- Redistributions in binary form must reproduce the above copyright notice, 
this list of conditions and the following disclaimer in the documentation 
and/or other materials provided with the distribution.


THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY 
EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES 
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT 
SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, 
SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT 
OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) 
HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR 
TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS 
SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 
 */
/*
 * 
 * This class is based on the article of John O'Conner
 * about using JavaDB in Desktop Applications available at
 * http://java.sun.com/developer/technicalArticles/J2SE/Desktop/javadb/
 *
 * We include below the original copyright
 * 
 * Copyright 2006 Sun Microsystems, Inc. ALL RIGHTS RESERVED Use of 
 * this software is authorized pursuant to the terms of the license 
 * found at http://developers.sun.com/berkeley_license.html.
 *
 */
package primerdb;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Vector;
import java.util.Properties;
import java.net.URLDecoder;

import primerdb.persistentTree.HierarchicalItem;

import org.w3c.dom.Element;

public class PrimerDao {

    /** Creates a new instance of AddressDao */
    public PrimerDao() {
        this("PrimerDatabase");
    }

    public PrimerDao(String primerName) {
        this.dbName = primerName;

        setDBSystemDir();
        dbProperties = loadDBProperties();
        String driverName = dbProperties.getProperty("derby.driver");
        loadDatabaseDriver(driverName);
        if (!dbExists()) {
            Boolean b = createDatabase();
            if (b) {
                System.out.println("Database created successfully");
            }
        }

    }

    private boolean dbExists() {

        // just a test
        // File test = new File(".");
        // System.out.println("Directory" + test.getAbsolutePath());

        boolean bExists = false;
        String dbLocation = getDatabaseLocation();
        File dbFileDir = new File(dbLocation);
        if (dbFileDir.exists()) {
            bExists = true;
        }
        return bExists;
    }

    private void setDBSystemDir() {
        // decide on the db system directory
        String userHomeDir = System.getProperty("user.home", ".");

        String path = PrimerDao.class.getProtectionDomain().getCodeSource().getLocation().getPath();
        try {
            String decodedPath = URLDecoder.decode(path, "UTF-8");

            String decodedPathNoJar = (new File(decodedPath)).getParentFile().getPath();

            System.err.println("Decoded path: " + decodedPathNoJar);

            String systemDir = decodedPathNoJar + "/.primerdb";
            System.setProperty("derby.system.home", systemDir);

            // create the db system directory
            File fileSystemDir = new File(systemDir);
            fileSystemDir.mkdir();
        } catch (Exception e) {
            System.err.println("Problem in obtaining directory where JAR file is");
        }
    }

    private void loadDatabaseDriver(String driverName) {
        // load Derby driver
        try {
            Class.forName(driverName);
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }
    }

    private Properties loadDBProperties() {
        InputStream dbPropInputStream = null;
        dbPropInputStream = PrimerDao.class.getResourceAsStream("Configuration.properties");
        dbProperties = new Properties();
        try {
            dbProperties.load(dbPropInputStream);
        } catch (IOException ex) {
            ex.printStackTrace();
        }
        return dbProperties;
    }

    private boolean createTables(Connection dbConnection) {
        boolean bCreatedTables = false;
        Statement statement = null;
        try {
            statement = dbConnection.createStatement();
            statement.execute(strCreatePrimerTable);
            statement.execute(strCreateProjectTable);
            statement.execute(strInsertRootFolder);
            bCreatedTables = true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return bCreatedTables;
    }

    private boolean createDatabase() {
        boolean bCreated = false;
        Connection dbConnection = null;

        String dbUrl = getDatabaseUrl();
        dbProperties.put("create", "true");

        try {
            dbConnection = DriverManager.getConnection(dbUrl, dbProperties);
            bCreated = createTables(dbConnection);
        } catch (SQLException ex) {
        }
        dbProperties.remove("create");
        return bCreated;
    }

    public boolean connect() {
        String dbUrl = getDatabaseUrl();
        try {
            dbConnection = DriverManager.getConnection(dbUrl, dbProperties);


            stmtSavePrimer = dbConnection.prepareStatement(strSavePrimer, Statement.RETURN_GENERATED_KEYS);
            stmtSaveFolder = dbConnection.prepareStatement(strSaveFolder, Statement.RETURN_GENERATED_KEYS);


            stmtUpdatePrimer = dbConnection.prepareStatement(strUpdatePrimer);
            stmtUpdateFolderName = dbConnection.prepareStatement(strUpdateFolderName);
            stmtUpdateFolderParent = dbConnection.prepareStatement(strUpdateFolderParent);

            stmtGetPrimer = dbConnection.prepareStatement(strGetPrimer);
            stmtGetFolder = dbConnection.prepareStatement(strGetFolder);

            stmtGetListPrimers = dbConnection.prepareStatement(strGetListPrimers);
            stmtGetListFolders = dbConnection.prepareStatement(strGetListFolders);
            stmtExportListPrimers = dbConnection.prepareStatement(strExportListPrimers);

            stmtDeletePrimersFolder = dbConnection.prepareStatement(strDeletePrimersFolder);

            stmtDeletePrimer = dbConnection.prepareStatement(strDeletePrimer);
            stmtDeleteFolder = dbConnection.prepareStatement(strDeleteFolder);


            isConnected = dbConnection != null;
        } catch (SQLException ex) {
            ex.printStackTrace();
            isConnected = false;
        }
        return isConnected;
    }

    private String getHomeDir() {
        return System.getProperty("user.home");
    }

    public void disconnect() {
        if (isConnected) {
            String dbUrl = getDatabaseUrl();
            dbProperties.put("shutdown", "true");
            try {
                DriverManager.getConnection(dbUrl, dbProperties);
            } catch (SQLException ex) {
            }
            isConnected = false;
        }
    }

    public String getDatabaseLocation() {
        String dbLocation = System.getProperty("derby.system.home") + "/" + dbName;
        return dbLocation;
    }

    public String getDatabaseUrl() {
        String dbUrl = dbProperties.getProperty("derby.url") + dbName;
        return dbUrl;
    }

    public int newPrimer(Primer record) {
        int id = -1;
        try {
            stmtSavePrimer.clearParameters();

            stmtSavePrimer.setString(1, record.getName());
            stmtSavePrimer.setString(2, record.getAnnTemp());
            stmtSavePrimer.setString(3, record.getFreezer());
            stmtSavePrimer.setString(4, record.getTarget());
            stmtSavePrimer.setString(5, record.getConc());
            stmtSavePrimer.setString(6, record.getDate());
            stmtSavePrimer.setString(7, record.getLabel());
            stmtSavePrimer.setString(8, record.getSequence());
            stmtSavePrimer.setString(9, record.getNotes());
            stmtSavePrimer.setString(10, record.getProject());
            stmtSavePrimer.setInt(11, record.getFolderId());
            int rowCount = stmtSavePrimer.executeUpdate();
            ResultSet results = stmtSavePrimer.getGeneratedKeys();
            if (results.next()) {
                id = results.getInt(1);
            }

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return id;
    }

    public boolean editPrimer(Primer p) {

        System.out.println("editPrimer with id=" + Integer.toString(p.getId()));

        boolean bEdited = false;
        try {
            stmtUpdatePrimer.clearParameters();

            stmtUpdatePrimer.setString(1, p.getName());
            stmtUpdatePrimer.setString(2, p.getAnnTemp());
            stmtUpdatePrimer.setString(3, p.getFreezer());
            stmtUpdatePrimer.setString(4, p.getTarget());
            stmtUpdatePrimer.setString(5, p.getConc());
            stmtUpdatePrimer.setString(6, p.getDate());
            stmtUpdatePrimer.setString(7, p.getLabel());
            stmtUpdatePrimer.setString(8, p.getSequence());
            stmtUpdatePrimer.setString(9, p.getNotes());
            stmtUpdatePrimer.setString(10, p.getProject());
            stmtUpdatePrimer.setInt(11, p.getFolderId());
            stmtUpdatePrimer.setInt(12, p.getId());
            stmtUpdatePrimer.executeUpdate();
            bEdited = true;
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return bEdited;

    }

    public boolean deletePrimer(int id) {

        System.out.println("Deleting primer " + id);

        boolean bDeleted = false;
        try {
            stmtDeletePrimer.clearParameters();
            stmtDeletePrimer.setInt(1, id);
            stmtDeletePrimer.executeUpdate();
            bDeleted = true;
        } catch (SQLException sqle) {
            System.out.println("Exception:");
            sqle.printStackTrace();
        }

        return bDeleted;
    }

    public Vector<Vector<String>> getListPrimers(int folderId) {

        System.out.println("getListPrimers function");

        Vector<Vector<String>> listEntries = new Vector<Vector<String>>();
        ResultSet results = null;

        try {

            stmtGetListPrimers.clearParameters();
            stmtGetListPrimers.setInt(1, folderId);


            results = stmtGetListPrimers.executeQuery();
            while (results.next()) {
                int id = results.getInt(1);
                String name = results.getString(2);
                String annTemp = results.getString(3);
                String freezer = results.getString(4);
                String target = results.getString(5);
                String conc = results.getString(6);
                String date = results.getString(7);
                Vector<String> p = new Vector<String>();
                p.add(Integer.toString(id));
                p.add(name);
                p.add(annTemp);
                p.add(freezer);
                p.add(target);
                p.add(conc);
                p.add(date);

                listEntries.add(p);
            }

        } catch (SQLException sqle) {
            sqle.printStackTrace();

        }
        System.out.println(listEntries.toString());
        return listEntries;
    }

    public Vector<Vector<String>> searchPrimers(String search) {

        System.out.println("searchPrimers function");

        Vector<Vector<String>> listEntries = new Vector<Vector<String>>();
        ResultSet results = null;



        String strSearchPrimers = "SELECT PRIMER.ID, PRIMER.NAME, PRIMER.ANNTEMP, FOLDER.ID,  PRIMER.TARGET, PRIMER.CONC, PRIMER.DATE FROM PRIMER, FOLDER "
                + "WHERE PRIMER.FOLDERID = FOLDER.ID AND "
                + "(PRIMER.NAME LIKE '%" + search + "%' OR "
                + "PRIMER.SEQUENCE LIKE '%" + search + "%' OR  "
                + "PRIMER.ANNTEMP LIKE '%" + search + "%' OR  "
                + "PRIMER.PROJECT LIKE '%" + search + "%' OR "
                + "PRIMER.FREEZER LIKE '%" + search + "%' OR "
                + "PRIMER.LABEL LIKE '%" + search + "%' OR "
                + "PRIMER.NOTES LIKE '%" + search + "%' OR "
                + "PRIMER.CONC LIKE '%" + search + "%' OR "
                + "PRIMER.DATE LIKE '%" + search + "%') "
                + "ORDER BY PRIMER.NAME ASC";

        try {

            Statement stmtSearchPrimers = dbConnection.createStatement();
            results = stmtSearchPrimers.executeQuery(strSearchPrimers);
            while (results.next()) {
                int primerId = results.getInt(1);
                String primerName = results.getString(2);
                String annTemp = results.getString(3);
                int folderId = results.getInt(4);
                String target = results.getString(5);
                String conc = results.getString(6);
                String date = results.getString(7);

                Vector<String> p = new Vector<String>();
                p.add(Integer.toString(primerId));
                p.add(primerName);
                p.add(annTemp);
                p.add(Integer.toString(folderId));
                p.add(target);
                p.add(conc);
                p.add(date);
                listEntries.add(p);
            }

        } catch (SQLException sqle) {
            sqle.printStackTrace();

        }
        System.out.println(listEntries.toString());
        return listEntries;
    }

    /*
     * This method returns all the primers that are in the folder folderId
     * and all its subfolders
     * 
     */
    public Vector<Vector<String>> exportListPrimers(int folderId) {

        System.out.println("exportListPrimers function");

        Vector<Vector<String>> listEntries = new Vector<Vector<String>>();
        ResultSet results = null;

        try {

            stmtExportListPrimers.clearParameters();
            stmtExportListPrimers.setInt(1, folderId);


            results = stmtExportListPrimers.executeQuery();

            while (results.next()) {

                String name = results.getString("NAME");
                String annTemp = results.getString("ANNTEMP");
                String freezer = results.getString("FREEZER");
                String target = results.getString("TARGET");
                String conc = results.getString("CONC");
                String date = results.getString("DATE");
                String label = results.getString("LABEL");
                String sequence = results.getString("SEQUENCE");
                String notes = results.getString("NOTES");
                String project = results.getString("PROJECT");

                Vector<String> p = new Vector<String>();
                p.add(name);
                p.add(project);
                p.add(freezer);
                p.add(label);
                p.add(date);
                p.add(target);
                p.add(sequence);
                p.add(notes);
                p.add(annTemp);
                p.add(conc);

                listEntries.add(p);
            }

        } catch (SQLException sqle) {
            sqle.printStackTrace();

        }
        System.out.println(listEntries.toString());
        return listEntries;
    }

    public Element exportPrimers(XMLExporter exporter, Element parentElem, int folderId, boolean shortlisted) {

        System.out.println("exportPrimers function");

        Folder folder = getFolder(folderId);
        //Folder folder = new Folder(0, "testOnly");

        Element folderElem = exporter.exportFolder(parentElem, folder);

        ResultSet results = null;

        try {
            stmtExportListPrimers.clearParameters();
            stmtExportListPrimers.setInt(1, folderId);

            results = stmtExportListPrimers.executeQuery();

            while (results.next()) {

                String name = results.getString("NAME");
                String annTemp = results.getString("ANNTEMP");
                String freezer = results.getString("FREEZER");
                String target = results.getString("TARGET");
                String conc = results.getString("CONC");
                String date = results.getString("DATE");
                String label = results.getString("LABEL");
                String sequence = results.getString("SEQUENCE");
                String notes = results.getString("NOTES");
                String project = results.getString("PROJECT");

                Primer primer = new Primer(name, annTemp, freezer, target, conc, date, label, sequence, notes, project, folderId);
                exporter.exportPrimer(folderElem, primer, shortlisted);
            }

        } catch (SQLException sqle) {
            sqle.printStackTrace();

        }

        return folderElem;

    }

    /*
     * This method returns all the primers that are in the folder folderId
     * and all its subfolders
     * 
     */
    public Vector<Vector<String>> exportRecursiveListPrimers(int folderId) {

        System.out.println("exportRecursiveListPrimers function");

        Vector<Vector<String>> listEntries = new Vector<Vector<String>>();
        ResultSet results = null;


        try {

            stmtExportListPrimers.clearParameters();
            stmtExportListPrimers.setInt(1, folderId);


            results = stmtExportListPrimers.executeQuery();

            while (results.next()) {

                String name = results.getString("NAME");
                String annTemp = results.getString("ANNTEMP");
                String freezer = results.getString("FREEZER");
                String target = results.getString("TARGET");
                String conc = results.getString("CONC");
                String date = results.getString("DATE");
                String label = results.getString("LABEL");
                String sequence = results.getString("SEQUENCE");
                String notes = results.getString("NOTES");
                String project = results.getString("PROJECT");

                Vector<String> p = new Vector<String>();

                p.add(name);
                p.add(project);
                p.add(freezer);
                p.add(label);
                p.add(date);
                p.add(target);
                p.add(sequence);
                p.add(notes);
                p.add(annTemp);
                p.add(conc);

                listEntries.add(p);
            }


        } catch (SQLException sqle) {
            sqle.printStackTrace();

        }


        // export subfolders

        Vector<Integer> v = getSubFolders(folderId);
        for (int i = 0; i < v.size(); i++) {
            listEntries.addAll(exportRecursiveListPrimers(v.get(i)));
        }



        System.out.println(listEntries.toString());

        // System.out.println(getSubFolders(folderId).toString());


        return listEntries;
    }

    public Element exportRecursivePrimers(XMLExporter exporter, Element parentElem, int folderId, boolean shortlisted) {

        System.out.println("exportRecursivePrimers function");
        System.out.println("filderId: " + folderId);
        // export this folder
        Element folderElem = exportPrimers(exporter, parentElem, folderId, shortlisted);

        // export subfolders

        Vector<Integer> v = getSubFolders(folderId);
        System.out.println("AMJAD: subfolders:" + v.toString());

        for (int i = 0; i < v.size(); i++) {
            exportRecursivePrimers(exporter, folderElem, v.get(i), shortlisted);
        }

        return folderElem;


//       System.out.println(listEntries.toString());

        // System.out.println(getSubFolders(folderId).toString());

    }

    /*
     * 
     * This method returns all the details of the primer with id index
     * 
     */
    public Primer getPrimer(int index) {
        System.out.println("Getting primer " + index);
        Primer primer = null;
        try {
            stmtGetPrimer.clearParameters();
            stmtGetPrimer.setInt(1, index);
            ResultSet result = stmtGetPrimer.executeQuery();
            if (result.next()) {
                String name = result.getString("NAME");
                String annTemp = result.getString("ANNTEMP");
                String freezer = result.getString("FREEZER");
                String target = result.getString("TARGET");
                String conc = result.getString("CONC");
                String date = result.getString("DATE");
                String label = result.getString("LABEL");
                String sequence = result.getString("SEQUENCE");
                String notes = result.getString("NOTES");
                String project = result.getString("PROJECT");
                int folderId = result.getInt("FOLDERID");
                int id = result.getInt("ID");
                primer = new Primer(name, annTemp, freezer, target, conc, date, label, sequence, notes, project, folderId, id);
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }

        return primer;
    }

    // ************************
    // Folder stuff
    // ************************
    public Folder getFolder(int id) {
        System.out.println("Getting folder " + id);
        Folder f = null;
        try {
            stmtGetFolder.clearParameters();
            stmtGetFolder.setInt(1, id);
            ResultSet result = stmtGetFolder.executeQuery();
            if (result.next()) {
                String name = result.getString("NAME");
                int parentId = result.getInt("PARENTID");

                f = new Folder(id, name, parentId);
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }

        return f;
    }

    /*
     * 
     * This method returns the list of folders that are subfolders of parentFolderId
     * 
     */
    public Vector<HierarchicalItem> getListFolders(int parentFolderId) {

        System.out.println("getListFolder function");

        Vector<HierarchicalItem> listEntries = new Vector<HierarchicalItem>();
        ResultSet results = null;

        try {

            stmtGetListFolders.clearParameters();
            stmtGetListFolders.setInt(1, parentFolderId);


            results = stmtGetListFolders.executeQuery();
            while (results.next()) {
                int id = results.getInt(1);
                String name = results.getString(2);

                Folder f = new Folder(id, name, parentFolderId);

                listEntries.add(f);
            }

        } catch (SQLException sqle) {
            sqle.printStackTrace();

        }
        System.out.println(listEntries.toString());
        return listEntries;
    }

    /*
     * This methods returns the list of ids of the subfolders of id
     * It is used by the exportRecursiveListPrimers
     * 
     */
    public Vector<Integer> getSubFolders(int id) {
        Vector<Integer> v = new Vector<Integer>();
        ResultSet results = null;

        boolean bDeleted = false;
        try {

            stmtGetListFolders.clearParameters();
            stmtGetListFolders.setInt(1, id);


            results = stmtGetListFolders.executeQuery();
            while (results.next()) {
                int idchild = results.getInt(1);
                v.add(idchild);
            }

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }

        return v;

    }

    public boolean deleteFolder(int id) {

        System.out.println("Deleting folder " + id);

        ResultSet results = null;

        boolean bDeleted = false;
        try {
            stmtDeleteFolder.clearParameters();
            stmtDeleteFolder.setInt(1, id);
            stmtDeleteFolder.executeUpdate();

            stmtDeletePrimersFolder.clearParameters();
            stmtDeletePrimersFolder.setInt(1, id);
            stmtDeletePrimersFolder.executeUpdate();


            stmtGetListFolders.clearParameters();
            stmtGetListFolders.setInt(1, id);


            results = stmtGetListFolders.executeQuery();
            while (results.next()) {
                int idchild = results.getInt(1);
                deleteFolder(idchild);
            }



            bDeleted = true;
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }

        return bDeleted;
    }

    public boolean editFolderName(HierarchicalItem f) {

        System.out.println("editFolder name with id=" + f.getId().toString());

        boolean bEdited = false;
        try {
            stmtUpdateFolderName.clearParameters();
            stmtUpdateFolderName.setString(1, f.getData().toString());
            stmtUpdateFolderName.setInt(2, Integer.parseInt(f.getId().toString()));
            stmtUpdateFolderName.executeUpdate();
            bEdited = true;
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return bEdited;

    }
    
    public boolean editFolderParent(HierarchicalItem f, Object id) {

        System.out.println("editFolder parent with id=" + f.getId().toString());
 
        boolean bEdited = false;
        try {
            stmtUpdateFolderParent.clearParameters();
            stmtUpdateFolderParent.setInt(1, Integer.parseInt(id.toString()));
            stmtUpdateFolderParent.setInt(2, Integer.parseInt(f.getId().toString()));
            stmtUpdateFolderParent.executeUpdate();
            bEdited = true;
            System.out.println("updated parent to: "+id.toString());
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return bEdited;

    }

    public int newFolder(HierarchicalItem f) {
        int id = -1;
        try {
            stmtSaveFolder.clearParameters();

            stmtSaveFolder.setString(1, f.getData().toString());
            stmtSaveFolder.setInt(2, (Integer) f.getParentId());
            int rowCount = stmtSaveFolder.executeUpdate();
            ResultSet results = stmtSaveFolder.getGeneratedKeys();
            if (results.next()) {
                id = results.getInt(1);
            }

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return id;
    }
//    public static void main(String[] args) {
//        PrimerDao db = new PrimerDao();
//        System.out.println(db.getDatabaseLocation());
//        System.out.println(db.getDatabaseUrl());
//        boolean b = db.connect();
//        
//        System.out.println(b);
//        
//        Project proj = new Project("testproj", 2);
//       
//        /*
//        Primer p = new Primer("test", "acddgd", "12C", proj);
//        
//        int id = db.newPrimer(p);
//        
//        p.setId(id);
//        
//        Primer newp = db.getPrimer(2);
//        
//        System.out.println(newp.toString());
//        */
//        
//        db.disconnect();
//    } 
    private int rootFolderId = 1;
    private Connection dbConnection;
    private Properties dbProperties;
    private boolean isConnected;
    private String dbName;
    private PreparedStatement stmtSavePrimer;
    private PreparedStatement stmtGetPrimer;
    private PreparedStatement stmtGetListPrimers;
    private PreparedStatement stmtUpdatePrimer;
    private PreparedStatement stmtDeletePrimer;
    private PreparedStatement stmtDeletePrimersFolder;
    private PreparedStatement stmtExportListPrimers;
    private static final String strCreatePrimerTable =
            "create table PRIMER ("
            + "    ID          INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
            + "    NAME        VARCHAR(100), "
            + "    FREEZER     VARCHAR(100), "
            + "    LABEL       VARCHAR(100), "
            + "    DATE        VARCHAR(100), "
            + "    TARGET      VARCHAR(100), "
            + "    SEQUENCE    VARCHAR(10000), "
            + "    NOTES       VARCHAR(10000), "
            + "    ANNTEMP     VARCHAR(100), "
            + "    CONC        VARCHAR(100), "
            + "    PROJECT     VARCHAR(1000), "
            + "    FOLDERID   INTEGER "
            + ")";
    private static final String strCreateProjectTable =
            "create table FOLDER ("
            + "    ID     INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
            + "    NAME   VARCHAR(100), "
            + "    PARENTID   INTEGER "
            + ")";
    private static final String strGetPrimer =
            "SELECT * FROM PRIMER "
            + "WHERE ID = ?";
    private static final String strSavePrimer =
            "INSERT INTO PRIMER "
            + "   (NAME, ANNTEMP, FREEZER, TARGET, CONC, DATE, LABEL, SEQUENCE, NOTES, PROJECT, FOLDERID) "
            + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    private static final String strGetListPrimers =
            "SELECT ID, NAME, ANNTEMP, FREEZER, TARGET, CONC, DATE FROM PRIMER "
            + "WHERE FOLDERID = ? "
            + "ORDER BY NAME ASC";
    private static final String strUpdatePrimer =
            "UPDATE PRIMER "
            + "SET NAME = ?, "
            + "    ANNTEMP = ?, "
            + "    FREEZER = ?, "
            + "    TARGET = ?, "
            + "    CONC = ?, "
            + "    DATE = ?, "
            + "    LABEL = ?, "
            + "    SEQUENCE = ?, "
            + "    NOTES = ?, "
            + "    PROJECT = ?, "
            + "    FOLDERID = ? "
            + "WHERE ID = ?";
    private static final String strDeletePrimer =
            "DELETE FROM PRIMER "
            + "WHERE ID = ?";
    // Folder stuff
    private PreparedStatement stmtGetListFolders;
    private PreparedStatement stmtSaveFolder;
    private PreparedStatement stmtUpdateFolderName;
    private PreparedStatement stmtUpdateFolderParent;
    private PreparedStatement stmtDeleteFolder;
    private PreparedStatement stmtGetFolder;
    private static final String strGetListFolders =
            "SELECT ID, NAME FROM FOLDER "
            + "WHERE PARENTID = ? "
            + "ORDER BY NAME ASC";
    private static final String strSaveFolder =
            "INSERT INTO FOLDER "
            + "(NAME, PARENTID) "
            + "VALUES (?, ?)";
    private static final String strUpdateFolderName =
            "UPDATE FOLDER "
            + "SET NAME = ? "
            + "WHERE ID = ?";
    private static final String strUpdateFolderParent =
            "UPDATE FOLDER "
            + "SET PARENTID = ? "
            + "WHERE ID = ?";
    private static final String strDeleteFolder =
            "DELETE FROM FOLDER "
            + "WHERE ID = ?";
    private static final String strDeletePrimersFolder =
            "DELETE FROM PRIMER "
            + "WHERE FOLDERID = ?";
    private static final String strGetFolder =
            "SELECT * FROM FOLDER "
            + "WHERE ID = ?";
    private String strInsertRootFolder = "INSERT INTO FOLDER (NAME, PARENTID) VALUES ('Root folder', 0)";
    //For comma separated file
    private String strExportListPrimers = "SELECT NAME, ANNTEMP, FREEZER, TARGET, CONC, DATE, LABEL, SEQUENCE, NOTES, PROJECT "
            + "FROM PRIMER WHERE FOLDERID = ? "
            + "ORDER BY NAME ASC";
}
